<?php


namespace commands;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class eFlightClientFee extends BaseCommand
{
    protected static $defaultName = 'eyb';
    const DEST_NAME1 = '';
    const BCJG = '国际业务分公司';//补偿机构

    public function configure()
    {
        parent::configure();
        $this->setDescription('e邮宝航空费用终端费清分表，压缩包名称：*EYB_*_excel.zip');
    }

    public function execute(InputInterface $input, OutputInterface $output)
    {
        parent::execute($input, $output);
        $dir = $this->getDataFilePath($input->getArgument('dir'));
        $this->unzip(['*EYB_*_excel.zip'], $dir, $output);
        $file = $this->mustFindFile($dir . '/temp/', '/^\d\d-.*\.csv$/', '==1');
        //var_dump($dir,$file);die();
        $output->writeln('<info>处理数据</info>');
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $row[2];
            $this->processField('zbqf', $city, $row, $n);
            $this->processField('tsdd', $city, $row, $n);
            $this->processField('bszf', $city, $row, $n);
            $this->processField('gjzdf', $city, $row, $n);
            $this->processField('ygf', $city, $row, $n);
            $this->processField('hhjcl', $city, $row, $n);
            $this->processField('gjjz', $city, $row, $n);
        }, 0);
        $dest = $dir . '/eyb_fqb.xlsx';

        $subtotal = [];
        $this->export($dest, './tpl/eyb_qfb.xlsx', function (Spreadsheet $spreadsheet) use (&$subtotal) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $this->processExport($this->data, $first_row, $sheet, $subtotal);
        });
        $this->export($dir . '/eyb_fqb2.xlsx', './tpl/eyb_qfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $first_row = $this->findFirstRowToRender($sheet);
            $subtotal = [];
            $this->processExport($this->data2, $first_row, $sheet, $subtotal);
        });
        $dest = $dir . '/eyb_bcb.xlsx';
        $this->export($dest, './tpl/eyb_bcb.xlsx', function (Spreadsheet $spreadsheet) use ($subtotal) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $row = $first_row;
            foreach ($this->data as $city => $data) {
                $sheet->setCellValue('A' . $row, $city);
                if ($city == self::BCJG) {
                    $sheet->setCellValueExplicit('B' . $row, $subtotal['D'], DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('C' . $row, $subtotal['H'], DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('D' . $row, $subtotal['G'], DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('E' . $row, $subtotal['I'], DataType::TYPE_NUMERIC);
                }
                $sheet->setCellValue('F' . $row, sprintf('=SUM(B%d:E%d)', $row, $row));
                $row++;
            }
            $this->setSheetSubtotal($sheet, $first_row, $row, 'F');
        });
        $this->rmTemp($dir, $output);
    }


    /**
     * 总部情分
     */
    public function zbqf($row, $n)
    {
        return floatval($row[getColIndex('l')]);
    }

    public function tsdd($row, $n)
    {
        return floatval($row[getColIndex('u')]);
    }

    public function bszf($row, $n)
    {
        if ((floatval($row[getColIndex('L')]) + floatval($row[getColIndex('T')])) == 0) {
            $price = $this->getFlightPrice($row[getColIndex('q')]);
            return $price * floatval($row[getColIndex('h')]);
        }
        return 0;
    }


    public function gjzdf($row, $n)
    {
        return floatval($row[getColIndex('K')]);
    }

    public function ygf($row)
    {
        //   return floatval($row[getColIndex('BU')]);
        return 0;
    }

    public function hhjcl($row)
    {
        $h = floatval($row[getColIndex('H')]);
        if ($h <= 3) {
            return 0.3;
        } else {
            return ceil($h - 3) * 0.15 + 0.3;
        }
    }

    public function gjjz($row)
    {
        return 0;
    }

    public function processExport($array, $first_row, Worksheet $sheet, &$subtotal = [])
    {
        $row = $first_row;
        foreach ($array as $city => $data) {
            $sheet->setCellValue('A' . $row, $city);
            $sheet->setCellValue('B' . $row, round($data['zbqf'], 2));
            $sheet->setCellValueExplicit('C' . $row, round($data['tsdd'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('D' . $row, round($data['bszf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('E' . $row, sprintf('=SUM(B%d:D%d)', $row, $row));
            $sheet->setCellValueExplicit('F' . $row, round($data['gjzdf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('G' . $row, round($data['ygf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('H' . $row, round($data['hhjcl'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('I' . $row, round($data['gjjz'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('J' . $row, sprintf('=SUM(E%d:I%d)', $row, $row));
            $row++;
        }
        $this->setSheetSubtotal($sheet, $first_row, $row, 'J', $subtotal);
    }

}
